﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;

namespace SSISLib
{
    public class SharePointListAPI
    {
        /// <summary>
        /// Initialisation
        /// </summary>
        public void InitialiseSession()
        {
            m_listNode = null;
            m_spName = "";
        }

        System.Xml.XmlNode m_listNode = null;
        string m_spName = "";

        public enum UpdateMode
        {
            Read = -1,
            Insert = 0,
            Update,
            Delete,
            Upsert
        }

        [Serializable]
        public class Binding
        {
            public string columnName, targetName;
            public string TargetName { get { return targetName ?? columnName; } }
            public string ssisType;
            public string binding;
            public bool bKey;
        }

        /// <summary>
        /// Data read...
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="url"></param>
        /// <param name="explicitUser"></param>
        /// <param name="explicitPassword"></param>
        /// <param name="queryData"></param>
        /// <returns></returns>
        public System.Collections.Generic.KeyValuePair<string, string>[] GetColumns(string spName, string url, string explicitUser, string explicitPassword, int timeOut, BlueIntegrator.DAL.DALBase dal)
        {
            try
            {
                // get hold of the SP interface
                SPLists.Lists spl = new SPLists.Lists(BlueIntegrator.Messaging.SettingTokenHelper.ResolveTokens(dal, url));

                if (explicitUser == null)
                    spl.Credentials = System.Net.CredentialCache.DefaultCredentials;
                else
                    spl.Credentials = new System.Net.NetworkCredential(BlueIntegrator.Messaging.SettingTokenHelper.ResolveTokens(dal, explicitUser), BlueIntegrator.Messaging.SettingTokenHelper.ResolveTokens(dal, BlueIntegrator.Helpers.Encryption.DecryptString(explicitPassword, "SSIS")));

                spName = BlueIntegrator.Messaging.SettingTokenHelper.ResolveTokens(dal, spName);

                if (url.EndsWith("/"))
                    url = url.Substring(0, url.Length - 1);

                if (!url.ToLower().EndsWith("asmx"))
                {
                    if (url.ToLower().IndexOf("_vti_bin") < 0)
                        spl.Url = url + "/_vti_bin/lists.asmx";
                    else
                        spl.Url = url + "/lists.asmx";
                }

                spl.Timeout = timeOut * 1000;

                System.Xml.XmlNode listNode = null;

                try
                {
                    if (m_spName == spName && m_listNode != null)
                    {
                        listNode = m_listNode;
                    }
                    else
                    {
                        m_listNode = listNode = spl.GetList(spName);
                        m_spName = spName;
                    }
                }
                catch (System.Web.Services.Protocols.SoapException exc)
                {
                    throw;
                }

                XmlDocument xmlDoc = new System.Xml.XmlDocument();
                XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element, "Query", "");
                XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "");
                XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "");

                ndQueryOptions.InnerXml = "<IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>" + "<DateInUtc>TRUE</DateInUtc>";

                List<string> fieldNames = new List<string>(), fieldTypes = new List<string>();
                string fieldXml = "";

                List<KeyValuePair<string, string>> result = new List<KeyValuePair<string, string>>();

                foreach (System.Xml.XmlNode node in listNode.SelectNodes("//*[local-name()='Field']"))
                {
                    if (node.Attributes["Name"] != null && node.Attributes["Type"] != null)
                    {
                        string type = node.Attributes["Type"].Value;

                        switch (type.ToUpper())
                        {
                            case "NUMBER":
                                type = "float";
                                break;

                            case "INTEGER":
                                type = "int";
                                break;

                            case "BOOLEAN":
                                type = "bit";
                                break;

                            case "DATETIME":
                                type = "datetime";
                                break;

                            case "GUID":
                                type = "uniqueidentifier";
                                break;

                            default:
                                type = "NVARCHAR(4000)";
                                break;
                        }

                        result.Add(new KeyValuePair<string, string>(node.Attributes["Name"].Value, type));
                    }
                }

                return result.ToArray();
            }
            catch (System.Web.Services.Protocols.SoapException exc)
            {
                System.Web.Services.Protocols.SoapException rethrownExc = new System.Web.Services.Protocols.SoapException(exc.Message + System.Environment.NewLine + (exc.Detail != null ? exc.Detail.InnerText : ""), exc.Code);
                throw rethrownExc;
            }
        }

        /// <summary>
        /// Data read...
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="url"></param>
        /// <param name="explicitUser"></param>
        /// <param name="explicitPassword"></param>
        /// <param name="queryData"></param>
        /// <returns></returns>
        public System.Xml.XmlDocument GetData(System.Collections.Hashtable properties, BlueIntegrator.DAL.DALBase dal, ref string paginationXml)
        {
            System.Collections.Hashtable queryData = new System.Collections.Hashtable();

            int nBindings = SafeGetInt(properties["NumTBindings"]);
            System.Runtime.Serialization.Formatters.Binary.BinaryFormatter bf = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
            bf.AssemblyFormat = System.Runtime.Serialization.Formatters.FormatterAssemblyStyle.Simple;
            bf.TypeFormat = System.Runtime.Serialization.Formatters.FormatterTypeStyle.TypesWhenNeeded;

            string camlQuery = properties["CAMLQuery"] as string;

            for (int i = 0; i < nBindings; i++)
            {
                string bindingInfo = properties["Binding:" + i] as string;

                if (bindingInfo != null)
                {
                    SharePointListAPI.Binding b = (SharePointListAPI.Binding)bf.Deserialize(new System.IO.MemoryStream(System.Convert.FromBase64String(bindingInfo)));

                    if (b.bKey && b.binding != null)
                    {
                        queryData[b.columnName] = BlueIntegrator.Messaging.SettingTokenHelper.ResolveTokens(dal, b.binding);
                    }
                    else if (b.bKey)
                    {
                        queryData[b.columnName] = null;
                    }
                }
            }

            string url = properties["SPURL"] as string;
            string list = properties["SPList"] as string;
            string user = properties["SPUser"] as string;
            string fields = properties["SPRelevantColumns"] as string ?? "";
            string pwd = BlueIntegrator.Helpers.Encryption.DecryptString(properties["SPPwd"] as string ?? "", "SSIS");
            int timeOut = SafeGetInt(properties["SPTimeout"], 60);
            bool attachments = BlueIntegrator.Helpers.Conversion.ConvertToBool(properties["ReadAttachments"]);

            System.Xml.XmlDocument doc = new XmlDocument();
            doc.LoadXml("<BlueIntegratorSharePoint/>");

            foreach (System.Collections.Hashtable h in GetData (list, url, user, pwd, queryData, ref paginationXml, timeOut, fields, camlQuery, attachments, dal))
            {
                System.Xml.XmlElement el = doc.CreateElement("Record");

                foreach (System.Collections.DictionaryEntry dent in h)
                {
                    System.Xml.XmlAttribute attr = doc.CreateAttribute(dent.Key as string);
                    attr.Value = dent.Value != null ? dent.Value.ToString() : "";
                    el.Attributes.Append(attr);
                }

                doc.DocumentElement.AppendChild(el);

                // add attachments
                if (attachments)
                {
                    foreach (System.Collections.DictionaryEntry dent in h)
                    {
                        if (dent.Key.ToString().StartsWith("Attachment:"))
                        {
                            System.Xml.XmlElement atel = doc.CreateElement("Attachment");
                            System.Xml.XmlAttribute atnm = doc.CreateAttribute("AttachmentName");
                            atnm.Value = dent.Key.ToString().Substring("Attachment:".Length);
                            atel.Attributes.Append(atnm);
                            System.Xml.XmlElement contel = doc.CreateElement("Base64Content");
                            contel.InnerText = System.Convert.ToBase64String((byte[])dent.Value);
                            atel.AppendChild(contel);
                        }
                    }
                }
            }

            return doc;
        }

        /// <summary>
        /// Conversion helper
        /// </summary>
        /// <param name="v"></param>
        /// <returns></returns>
        private int SafeGetInt(object v)
        {
            return SafeGetInt(v, 0);
        }

        /// <summary>
        /// Conversion helper
        /// </summary>
        /// <param name="v"></param>
        /// <returns></returns>
        private int SafeGetInt(object v, int def)
        {
            if (v == null)
                return def;

            try
            {
                return System.Convert.ToInt32(v);
            }
            catch
            {
            }

            return def;
        }
        
        /// <summary>
        /// Data read...
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="url"></param>
        /// <param name="explicitUser"></param>
        /// <param name="explicitPassword"></param>
        /// <param name="queryData"></param>
        /// <returns></returns>
        public System.Collections.Hashtable[] GetData(string spName, string url, string explicitUser, string explicitPassword, System.Collections.Hashtable queryData, ref string paginationXml, int timeOut, string _relevantFields, string explicitQuery, bool returnAttachments, BlueIntegrator.DAL.DALBase dal)
        {
            string relevantFields = "\\ID\\" + _relevantFields;  // ID is always relevant

            try
            {
                explicitPassword = BlueIntegrator.Messaging.SettingTokenHelper.ResolveTokens(dal, BlueIntegrator.Helpers.Encryption.DecryptString(explicitPassword, "SSIS"));

                // get hold of the SP interface
                SPLists.Lists spl = new SPLists.Lists(url);
                List<System.Collections.Hashtable> result = new List<System.Collections.Hashtable>();

                if (explicitUser == null)
                    spl.Credentials = System.Net.CredentialCache.DefaultCredentials;
                else
                    spl.Credentials = new System.Net.NetworkCredential(explicitUser, explicitPassword);

                if (url.EndsWith("/"))
                    url = url.Substring(0, url.Length - 1);

                if (!url.ToLower().EndsWith("asmx"))
                {
                    if (url.ToLower().IndexOf("_vti_bin") < 0)
                        spl.Url = url + "/_vti_bin/lists.asmx";
                    else
                        spl.Url = url + "/lists.asmx";
                }

                spl.Timeout = timeOut * 1000;

                System.Xml.XmlNode listNode = null;
                string id = "?";

                try
                {
                    if (m_spName == spName && m_listNode != null)
                    {
                        listNode = m_listNode;
                    }
                    else
                    {
                        m_listNode = listNode = spl.GetList(spName);
                        m_spName = spName;
                    }
                }
                catch
                {
                }

                XmlDocument xmlDoc = new System.Xml.XmlDocument();
                XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element, "Query", "");
                XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "");
                XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "");

                ndQueryOptions.InnerXml = paginationXml + "<IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>" + "<DateInUtc>TRUE</DateInUtc>";
                paginationXml = null;

                List<string> fieldNames = new List<string>(), fieldTypes = new List<string>();
                string fieldXml = "";

                foreach (System.Xml.XmlNode node in listNode.SelectNodes("//*[local-name()='Field']"))
                {
                    if (node.Attributes["Name"] != null && node.Attributes["Type"] != null)
                    {
                        string name = node.Attributes["Name"].Value;
                        if (relevantFields.Contains("\\" + name + "\\"))
                        {
                            fieldXml += "<FieldRef Name='" + name + "'/>";
                        }
                        fieldNames.Add(name);
                        fieldTypes.Add(node.Attributes["Type"].Value);
                    }
                }

                string innerXml = "<Where>";

                if (queryData.Count >= 1)
                {
                    if (queryData.Count > 1)
                    {
                        innerXml += "<And>";
                    }

                    foreach (string key in queryData.Keys)
                    {
                        object value = queryData[key];

                        if (value == null || value == System.DBNull.Value)
                        {
                            innerXml += "<IsNull><FieldRef Name='" + key + "' /></IsNull>";
                        }
                        else
                        {
                            string colType = "Text";

                            if (value is double || value is float || value is decimal)
                            {
                                colType = "Number";
                            }
                            else if (value is int || value is Int16 || value is UInt32)
                            {
                                colType = "Integer";
                            }
                            else if (value is bool)
                            {
                                colType = "Boolean";
                            }
                            else if (value is System.Guid)
                            {
                                colType = "Guid";
                            }
                            else if (value is System.DateTime || value is System.TimeSpan)
                            {
                                colType = "DateTime";
                            }
                            else
                            {
                                colType = "Text";
                            }

                            if (value is string && value.ToString().StartsWith("[") && value.ToString().EndsWith("]") && fieldNames.IndexOf(value.ToString().Replace("[", "").Replace("]", "")) >= 0)
                            {
                                innerXml += "<Eq><FieldRef Name='" + key + "' /><FieldRef Name='" + value.ToString().Replace("[", "").Replace("]", "") + "' /></Eq>";
                            }
                            else
                            {
                                innerXml += "<Eq><FieldRef Name='" + key + "' /><Value Type='" + colType + "'>" + queryData[key] + "</Value></Eq>";
                            }
                        }

                        if (queryData.Count > 1)
                        {
                            innerXml += "</And>";
                        }

                        innerXml += "</Where>";
                    }
                }
                else
                {
                    innerXml += "</Where>";
                }

                ndQuery.InnerXml = innerXml;

                if (explicitQuery != null && explicitQuery.Trim() != "")
                {
                    System.Xml.XmlDocument doc = new XmlDocument();
                    doc.LoadXml(explicitQuery);
                    ndQuery.InnerXml = doc.DocumentElement.InnerXml;
                }

                try
                {
                    ndViewFields.InnerXml = fieldXml;
                    XmlNode ndListItems =
                        spl.GetListItems(spName, null, (ndQuery.ChildNodes.Count > 0 && ndQuery.InnerXml != "<Where></Where>") ? ndQuery : null,
                        ndViewFields, null, ndQueryOptions, null);

                    bool found = false;

                    System.Xml.XmlNode paginationResponse = ndListItems.SelectSingleNode("//@ListItemCollectionPositionNext");

                    if (paginationResponse != null) // is there further data?...
                        paginationXml = "<Paging ListItemCollectionPositionNext='" + paginationResponse.InnerXml + "' />";

                    // populate hash
                    foreach (System.Xml.XmlNode rowNode in ndListItems.SelectNodes("//*[@ows_ID != '']"))
                    {
                        System.Collections.Hashtable thisRow = new System.Collections.Hashtable();
                        int n = 0;
                        string thisId = null;

                        foreach (string fieldName in fieldNames)
                        {
                            if (fieldName == "ID")
                            {
                                System.Xml.XmlAttribute attr = rowNode.Attributes["ows_" + fieldName];
                                thisId = attr.Value;
                            }

                            if (relevantFields.Contains("\\" + fieldName + "\\"))
                            {
                                System.Xml.XmlAttribute attr = rowNode.Attributes["ows_" + fieldName];

                                object val = attr != null ? attr.Value : null;
                                string type = fieldTypes[n];

                                if (val != null)
                                {
                                    try
                                    {
                                        switch (type.ToUpper())
                                        {
                                            case "NUMBER":
                                                val = System.Convert.ToDouble(val);
                                                break;

                                            case "INTEGER":
                                                val = System.Convert.ToInt32(val);
                                                break;

                                            case "BOOLEAN": // improved bool parsing 17/04
                                                if (val.ToString() == "1")
                                                    val = true;
                                                else if (val.ToString() == "0")
                                                    val = false;
                                                else
                                                    val = System.Convert.ToBoolean(val);
                                                break;

                                            case "DATETIME":
                                                val = System.Convert.ToDateTime(val);
                                                break;

                                            case "GUID":
                                                val = new System.Guid(System.Convert.ToString(val));
                                                break;

                                            default:
                                                val = System.Convert.ToString(val);
                                                break;
                                        }
                                    }
                                    catch (System.Exception exc)
                                    {
                                        throw new Exception("Error converting field " + fieldName + " from " + val + " to " + type + " : " + exc);
                                    }
                                }

                                thisRow[fieldName] = val;
                                n++;
                            }
                        }

                        if (thisId != null && returnAttachments)
                        {
                            XmlNode attachmentNode = spl.GetAttachmentCollection(spName, thisId);

                            foreach (System.Xml.XmlNode attachment in attachmentNode.SelectNodes("//*[local-name()='Attachment']"))
                            {
                                try
                                {
                                    string aurl = attachment.InnerText;

                                    System.Net.WebRequest req = System.Net.WebRequest.Create(aurl);
                                    req.Credentials = spl.Credentials;
                                    req.Method = "GET";
                                    System.Net.WebResponse wr = req.GetResponse();

                                    byte[] content = BlueIntegrator.DAL.DALBase.ConvertToMemoryStream(wr.GetResponseStream()).ToArray();

                                    string name = aurl;

                                    if (name.Contains("/"))
                                        name = name.Substring(name.LastIndexOf("/") + 1);

                                    thisRow["Attachment:" + name] = content;
                                }
                                catch (System.Web.Services.Protocols.SoapException exc)
                                {
                                    System.Web.Services.Protocols.SoapException rethrownExc = new System.Web.Services.Protocols.SoapException(exc.Message + System.Environment.NewLine + (exc.Detail != null ? exc.Detail.InnerText : ""), exc.Code);
                                    throw new Exception("Error retrieving attachment " + attachment.OuterXml, rethrownExc);
                                }
                                catch (System.Exception exc)
                                {
                                    throw new Exception("Error retrieving attachment " + attachment.OuterXml, exc);
                                }
                            }
                        }

                        result.Add(thisRow);
                    }
                }
                catch (System.Web.Services.Protocols.SoapException ex)
                {
                    throw;
                }

                return result.ToArray();
            }
            catch (System.Web.Services.Protocols.SoapException exc)
            {
                System.Web.Services.Protocols.SoapException rethrownExc = new System.Web.Services.Protocols.SoapException(exc.Message + System.Environment.NewLine + (exc.Detail != null ? exc.Detail.InnerText : ""), exc.Code);
                throw rethrownExc;
            }
        }

        /// <summary>
        /// Write data Xml variant
        /// </summary>
        /// <param name="properties"></param>
        /// <param name="data"></param>
        public void WriteData(System.Collections.Hashtable properties, System.Xml.XmlDocument data, BlueIntegrator.DAL.DALBase dal)
        {
            string url = properties["SPURL"] as string;
            string list = properties["SPList"] as string;
            string user = properties["SPUser"] as string;
            string fields = properties["SPRelevantColumns"] as string ?? "";

            string pwd = BlueIntegrator.Helpers.Encryption.DecryptString(properties["SPPwd"] as string ?? "", "SSIS");
            int timeOut = SafeGetInt(properties["SPTimeout"], 60);
            System.Collections.Hashtable keys = System.Collections.Specialized.CollectionsUtil.CreateCaseInsensitiveHashtable();
            System.Collections.Hashtable values = System.Collections.Specialized.CollectionsUtil.CreateCaseInsensitiveHashtable();
            List<AttachmentData> attachments = new List<AttachmentData>();

            // for each record
            foreach (System.Xml.XmlNode record in data.DocumentElement.ChildNodes)
            {
                UpdateMode mode = (UpdateMode)SafeGetInt(properties["SPMode"]);
                keys.Clear();
                values.Clear();
                attachments.Clear();

                if (record.Name == "Record")
                {
                    foreach (XmlAttribute a in record.Attributes)
                    {
                        values[a.Name] = a.Value;
                    }

                    // is there a Action or Attachment record
                    foreach (System.Xml.XmlNode child in record.ChildNodes)
                    {
                        if (child is XmlElement && child.Name == "Action")
                        {
                            // capture action type...
                            XmlAttribute actionType = child.Attributes["ActionType"];

                            if (actionType != null)
                            {
                                if (actionType.Value.ToLower() == "insert")
                                {
                                    mode = SharePointListAPI.UpdateMode.Insert;
                                }
                                else if (actionType.Value.ToLower() == "update")
                                {
                                    mode = SharePointListAPI.UpdateMode.Update;
                                }
                                else if (actionType.Value.ToLower() == "delete")
                                {
                                    mode = SharePointListAPI.UpdateMode.Delete;
                                }
                                else if (actionType.Value.ToLower() == "upsert")
                                {
                                    mode = SharePointListAPI.UpdateMode.Upsert;
                                }
                                else
                                {
                                    throw new ApplicationException("Invalid ActionType : " + actionType.Value);
                                }
                            }

                            foreach (System.Xml.XmlNode child2 in child.ChildNodes)
                            {
                                if (child2 is XmlElement && child2.Name == "Key")
                                {
                                    foreach (XmlAttribute a in child2.Attributes)
                                    {
                                        keys[a.Name] = a.Value;
                                    }
                                }
                            }
                        }
                        else if (child is XmlElement && child.Name == "Attachment")
                        {
                            System.Xml.XmlAttribute nameAttr = child.Attributes["AttachmentName"];
                            System.Xml.XmlAttribute actionAttr = child.Attributes["AttachmentAction"];

                            // make attachments...
                            AttachmentData ad = new AttachmentData();
                            ad.action = (actionAttr != null ? actionAttr.Value : null);
                            ad.name = (nameAttr != null ? nameAttr.Value : null);

                            // find content
                            foreach (System.Xml.XmlNode child2 in child.ChildNodes)
                            {
                                if (child2 is System.Xml.XmlElement && child2.LocalName == "Base64Content")
                                    ad.content = System.Convert.FromBase64String(child2.InnerText.Trim());
                            }

                            attachments.Add(ad);
                        }
                    }

                    WriteData(list, url, user, pwd, values, mode, keys, timeOut, attachments, dal);
                }
            }
        }

        /// <summary>
        /// Attachment helper class
        /// </summary>
        public class AttachmentData
        {
            public byte[] content;
            public string name;
            public string action;
        }

        /// <summary>
        /// Data write...
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="url"></param>
        /// <param name="explicitUser"></param>
        /// <param name="explicitPassword"></param>
        /// <param name="data"></param>
        /// <param name="mode"></param>
        /// <param name="keys"></param>
        public void WriteData(string spName, string url, string explicitUser, string explicitPassword, System.Collections.Hashtable data, UpdateMode mode, System.Collections.Hashtable keys, int timeOut, List<AttachmentData> attachments, BlueIntegrator.DAL.DALBase dal)
        {
            try
            {
                explicitPassword = BlueIntegrator.Messaging.SettingTokenHelper.ResolveTokens(dal, BlueIntegrator.Helpers.Encryption.DecryptString(explicitPassword, "SSIS"));

                // get hold of the SP interface
                SPLists.Lists spl = new SPLists.Lists(url);
                if (explicitUser == null)
                    spl.Credentials = System.Net.CredentialCache.DefaultCredentials;
                else
                    spl.Credentials = new System.Net.NetworkCredential(explicitUser, explicitPassword);

                if (url.EndsWith("/"))
                    url = url.Substring(0, url.Length - 1);

                if (!url.ToLower().EndsWith("asmx"))
                {
                    if (url.ToLower().IndexOf("_vti_bin") < 0)
                        spl.Url = url + "/_vti_bin/lists.asmx";
                    else
                        spl.Url = url + "/lists.asmx";
                }

                spl.Timeout = timeOut * 1000;

                System.Xml.XmlNode listNode = null;
                List<string> ids = new List<string>();
                ids.Add("?");

                try
                {
                    if (m_spName == spName && m_listNode != null)
                    {
                        listNode = m_listNode;
                    }
                    else
                    {
                        m_listNode = listNode = spl.GetList(spName);
                        m_spName = spName;
                    }
                }
                catch
                {
                }

                if (listNode == null)
                {
                    spl.AddList(spName, "BlueIntegrator " + spName + " I/O Queue", 100);
                    listNode = spl.GetList(spName);

                    if (listNode == null)
                        throw new Exception("SharePoint list :" + spName + " does not exist and cannot be created");
                }

                if (mode == UpdateMode.Update || mode == UpdateMode.Upsert || mode == UpdateMode.Delete)
                {
                    // query for existing
                    // Get current SharePoint messages
                    XmlDocument xmlDoc = new System.Xml.XmlDocument();
                    XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element, "Query", "");
                    XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "");
                    XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "");

                    ndQueryOptions.InnerXml = "<IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>" + "<DateInUtc>TRUE</DateInUtc>";
                    ndViewFields.InnerXml = "<FieldRef Name='ID' />";
                    string innerXml = "<Where>";

                    if (keys.Count >= 1)
                    {
                        if (keys.Count > 1)
                        {
                            innerXml += "<And>";
                        }

                        foreach (string key in keys.Keys)
                        {
                            object value = data[key];
                            string colType = "Text";

                            if (value is double || value is float || value is decimal)
                            {
                                colType = "Number";
                            }
                            else if (value is int || value is Int16 || value is UInt32)
                            {
                                colType = "Integer";
                            }
                            else if (value is bool)
                            {
                                colType = "Boolean";
                            }
                            else if (value is System.Guid)
                            {
                                colType = "Guid";
                            }
                            else if (value is System.DateTime || value is System.TimeSpan)
                            {
                                colType = "DateTime";
                            }
                            else
                            {
                                colType = "Text";
                            }

                            innerXml += "<Eq><FieldRef Name='" + key + "' /><Value Type='" + colType + "'>" + keys[key] + "</Value></Eq>";
                        }

                        if (keys.Count > 1)
                        {
                            innerXml += "</And>";
                        }

                        innerXml += "</Where>";
                    }
                    else
                    {
                        throw new Exception("Cannot Update - no key fields specified");
                    }

                    ndQuery.InnerXml = innerXml;
                    System.Collections.Hashtable existingMessages = new System.Collections.Hashtable();

                    try
                    {
                        XmlNode ndListItems =
                            spl.GetListItems(spName, null, ndQuery,
                            ndViewFields, null, ndQueryOptions, null);

                        bool found = false;

                        // populate hash of messageIDs
                        ids.Clear();

                        foreach (System.Xml.XmlNode rowNode in ndListItems.SelectNodes("//*[@ows_ID != '']"))
                        {
                            ids.Add(rowNode.Attributes["ows_ID"].Value);
                            found = true;
                        }

                        if (found && !(mode == UpdateMode.Update || mode == UpdateMode.Delete))
                        {
                            return;
                        }
                        else
                            if (found)
                            {
                                if (mode == UpdateMode.Upsert)
                                    mode = UpdateMode.Update;
                            }
                    }
                    catch (System.Web.Services.Protocols.SoapException ex)
                    {
                        throw;
                    }
                }

                // column storage
                /*
                System.Collections.Hashtable columns = new System.Collections.Hashtable();

                // now do operation...
                if (mode != UpdateMode.Delete)
                foreach (System.Collections.DictionaryEntry dent in data)
                {
                    // maybe add column to list
                    if (columns[dent.Key.ToString()] == null)
                    {
                        string xPath = "//*[local-name()='Field' and @Name='" + dent.Key.ToString() + "']";
                        System.Xml.XmlNode fieldNode = listNode.SelectSingleNode(xPath);

                        if (fieldNode == null)
                        {
                            // need to add column...
                            XmlNode ndVersion = listNode.Attributes["Version"];
                            XmlNode ndID = listNode.Attributes["ID"];
                            System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();

                            XmlNode ndDeleteFields = xmlDoc.CreateNode(XmlNodeType.Element,
                                "Fields", "");
                            XmlNode ndProperties = xmlDoc.CreateNode(XmlNodeType.Element, "List",
                                "");
                            XmlAttribute ndTitleAttrib =
                                (XmlAttribute)xmlDoc.CreateNode(XmlNodeType.Attribute,
                                "Title", "");
                            XmlAttribute ndDescriptionAttrib =
                                (XmlAttribute)xmlDoc.CreateNode(XmlNodeType.Attribute,
                                "Description", "");
                            XmlNode ndNewFields = xmlDoc.CreateNode(XmlNodeType.Element,
                                "Fields", "");
                            XmlNode ndUpdateFields = xmlDoc.CreateNode(XmlNodeType.Element,
                                "Fields", "");

                            // determine type
                            string colType = "Text";

                            if (dent.Value is double || dent.Value is float || dent.Value is decimal)
                            {
                                colType = "Number";
                            }
                            else if (dent.Value is int || dent.Value is Int16 || dent.Value is UInt32)
                            {
                                colType = "Integer";
                            }
                            else if (dent.Value is bool)
                            {
                                colType = "Boolean";
                            }
                            else if (dent.Value is System.Guid)
                            {
                                colType = "Guid";
                            }
                            else if (dent.Value is System.DateTime || dent.Value is System.TimeSpan)
                            {
                                colType = "DateTime";
                            }
                            else
                            {
                                colType = "Text";
                            }

                            ndNewFields.InnerXml = "<Method ID='1'>" +
                               "<Field Type='" + colType + "' DisplayName='" + dent.Key.ToString()
                                + "' FromBaseType='TRUE'/>" + "</Method>";

                            try
                            {
                                XmlNode ndReturn =
                                   spl.UpdateList(ndID.Value,
                                   ndProperties, ndNewFields, null, null,
                                   ndVersion.Value);
                            }
                            catch (Exception ex)
                            {
                                throw;
                            }

                            listNode = spl.GetList(spName);
                        }
                        columns[dent.Key.ToString()] = true;
                    }
                }*/

                // now update database...
                foreach (string id in ids)
                {
                    // now add entry to list, since it's not in SharePoint...
                    string strBatch = "<Method ID=\"1\" Cmd=\"New\">";

                    if (mode == UpdateMode.Update || mode == UpdateMode.Delete)
                    {
                        if (mode == UpdateMode.Update)
                        {
                            strBatch = "<Method ID=\"1\" Cmd=\"Update\">";
                        }
                        else if (mode == UpdateMode.Delete)
                        {
                            strBatch = "<Method ID=\"1\" Cmd=\"Delete\">";
                        }
                            
                        strBatch += "<Field Name=\"ID\">" + id + "</Field>";
                    }

                    if (mode != UpdateMode.Delete)
                    {
                        // add all the other columns
                        foreach (System.Collections.DictionaryEntry dent2 in data)
                        {
                            strBatch += "<Field Name=\"" + dent2.Key.ToString() + "\">" + System.Security.SecurityElement.Escape(dent2.Value != null ? dent2.Value.ToString() : "") + "</Field>";
                        }
                    }

                    strBatch += "</Method>";

                    System.Xml.XmlDocument xmlDoc2 = new System.Xml.XmlDocument();
                    System.Xml.XmlElement elBatch = xmlDoc2.CreateElement("Batch");
                    elBatch.SetAttribute("OnError", "Continue");
                    XmlNode ndID2 = listNode.Attributes["ID"];
                    elBatch.SetAttribute("ListVersion", ndID2.Value);
                    elBatch.InnerXml = strBatch;

                    try
                    {
                        XmlNode ndReturn2 = spl.UpdateListItems(spName, elBatch);

                        if (attachments.Count > 0)
                        {
                            string rowId = id;

                            if (rowId == "?")
                            {
                                XmlNode rowNode = ndReturn2.SelectSingleNode("//*[local-name()='row']");

                                if (rowNode != null && rowNode.Attributes["owd_ID"] != null)
                                {
                                    rowId = rowNode.Attributes["ows_ID"].Value;
                                }
                            }

                            foreach (AttachmentData ad in attachments)
                            {
                                try
                                {
                                    try
                                    {
                                        string action = (ad.action ?? "").ToUpper();

                                        if (action == "" || action == "DELETE" || action == "UPDATE" || action == "UPSERT")
                                        {
                                            try
                                            {
                                                // get url
                                                System.Xml.XmlNode attachmentColl = spl.GetAttachmentCollection(spName, rowId);
                                                foreach (System.Xml.XmlNode attachment in attachmentColl.SelectNodes("//*[local-name()='Attachment']"))
                                                {
                                                    string aurl = attachment.InnerText.Trim();

                                                    if (aurl.EndsWith("/" + ad.name))
                                                    {
                                                        spl.DeleteAttachment(spName, rowId, aurl);
                                                    }
                                                }
                                            }
                                            catch
                                            {
                                                if (action == "DELETE")
                                                    throw;
                                            }
                                        }

                                        if (action != "DELETE")
                                        {
                                            spl.AddAttachment(spName, rowId, ad.name, ad.content);
                                        }
                                    }
                                    catch (System.Web.Services.Protocols.SoapException exc)
                                    {
                                        System.Web.Services.Protocols.SoapException rethrownExc = new System.Web.Services.Protocols.SoapException(exc.Message + System.Environment.NewLine + (exc.Detail != null ? exc.Detail.InnerText : ""), exc.Code);
                                        throw rethrownExc;
                                    }
                                }
                                catch (System.Exception exc)
                                {
                                    throw new Exception("Error processing attachment " + ad.name, exc);
                                }
                            }
                        }
                    }
                    catch (System.Exception exc)
                    {
                        throw;
                    }
                }
            }
            catch (System.Web.Services.Protocols.SoapException exc)
            {
                System.Web.Services.Protocols.SoapException rethrownExc = new System.Web.Services.Protocols.SoapException(exc.Message + System.Environment.NewLine + (exc.Detail != null ? exc.Detail.InnerText : ""), exc.Code);
                throw rethrownExc;
            }
        }
    }
}
